﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// Summary description for DataAccessLayer
/// </summary>
public class DataAccessLayer
{
    public DataAccessLayer()
    {

    }
    /// <summary>
    /// Add the stored procedures and Input values into the sqlcommand object
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    public static SqlCommand GetDataCommand(string sql, Hashtable param)
    {
        try
        {
            //Declare the SqlCommand and assign sql statements
            SqlCommand sqlCom = new SqlCommand();
            sqlCom.CommandText = sql;
            sqlCom.CommandType = CommandType.StoredProcedure;
            sqlCom.Connection = GetConnection();

            //Check the input hashtable is empty or not
            if (param != null)
            {
                if (param.Count > 0)
                {
                    //Loop thru the hashtable input values and add these into sqlcommand parameter
                    foreach (string hshKey in param.Keys)
                    {
                        sqlCom.Parameters.Add(new SqlParameter(hshKey, param[hshKey]));
                    }
                }
            }

            //Return the sqlcommand 
            return sqlCom;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Add the stored procedures to the Sqlcommand
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static SqlCommand GetDataCommand(string sql)
    {
        try
        {
            //Declare and set the stored procedures into sqlcommand
            SqlCommand sqlCom = new SqlCommand();
            sqlCom.CommandText = sql;
            sqlCom.CommandType = CommandType.StoredProcedure;
            sqlCom.Connection = GetConnection();

            //Return the sqlcommand
            return sqlCom;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Execuete the stored procedure with input values
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    public static void ExecuteCommand(string sql, Hashtable param)
    {
        try
        {
            //Get the stored procedure and input values and execute the sqlcommand
            SqlCommand sqlCom = GetDataCommand(sql, param);
            sqlCom.ExecuteNonQuery();

            //Close the sql connection and dispose the sqlcommand and connection
            sqlCom.Connection.Close();
            sqlCom.Connection.Dispose();
            sqlCom.Dispose();
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
        }
    }

    /// <summary>
    /// Execuete the stored procedure with input values
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    public static int ExecuteCommand_RowsAffected(string sql, Hashtable param)
    {
        try
        {
            //Get the stored procedure and input values and execute the sqlcommand
            SqlCommand sqlCom = GetDataCommand(sql, param);
            int count = sqlCom.ExecuteNonQuery();

            //Close the sql connection and dispose the sqlcommand and connection
            sqlCom.Connection.Close();
            sqlCom.Connection.Dispose();
            sqlCom.Dispose();
            return count;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return 0;
        }
    }

    /// <summary>
    /// Execute the stored procedure
    /// </summary>
    /// <param name="sql"></param>
    public static void ExecuteCommand(string sql)
    {
        try
        {
            //Get the stored procedure and execute the sqlcommand
            SqlCommand sqlCom = GetDataCommand(sql);
            sqlCom.ExecuteNonQuery();

            //Close the sql connection and dispose the sqlcommand and connection
            sqlCom.Connection.Close();
            sqlCom.Connection.Dispose();
            sqlCom.Dispose();
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
        }
    }

    /// <summary>
    /// Execute the sql for to return the only one specific value
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    public static void ExecuteScaler(string sql, Hashtable param)
    {
        try
        {
            //Get the stored procedure and input values and execute the sqlcommand
            SqlCommand sqlCom = GetDataCommand(sql, param);
            sqlCom.ExecuteScalar();

            //Close the sql connection and dispose the sqlcommand and connection
            sqlCom.Connection.Close();
            sqlCom.Connection.Dispose();
            sqlCom.Dispose();
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
        }
    }

    /// <summary>
    /// Get the data from DataReader with specific arguments
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static SqlDataReader GetDataReader(string sql, Hashtable param)
    {
        try
        {
            //Return the specific data to datareader
            return GetDataCommand(sql, param).ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Get the data from sql for specific input and fill record into dataset and return dataset
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static DataSet GetDataSet(string sql, Hashtable param)
    {
        try
        {
            //Declare the dataset and dataadapter object
            DataSet dsData = new DataSet();
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            //Fill the data into dataset from dataadapter
            sqlAdap.SelectCommand = GetDataCommand(sql, param);
            sqlAdap.Fill(dsData);
            sqlAdap.SelectCommand.Connection.Close();
            sqlAdap.SelectCommand.Dispose();
            sqlAdap.Dispose();

            //Return the dataset
            return dsData;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Get teh data from sql and return the dataset
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataSet GetDataSet(string sql)
    {
        try
        {
            //Declare the dataset and dataadapter object
            DataSet dsData = new DataSet();
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            //Fill the data into dataset from dataadapter
            sqlAdap.SelectCommand = GetDataCommand(sql);
            sqlAdap.Fill(dsData);
            sqlAdap.SelectCommand.Connection.Close();
            sqlAdap.SelectCommand.Dispose();
            sqlAdap.Dispose();

            //Return the dataset
            return dsData;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Return Datatable for specific sql with inputs
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql, Hashtable param)
    {
        try
        {
            //Declare the datatable and dataadapter object
            DataTable dtData = new DataTable();
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            //Fill the data into dataset from dataadapter
            sqlAdap.SelectCommand = GetDataCommand(sql, param);
            sqlAdap.Fill(dtData);
            sqlAdap.SelectCommand.Connection.Close();
            sqlAdap.SelectCommand.Dispose();
            sqlAdap.Dispose();

            //Return the datatable
            return dtData;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;            
        }
    }

    /// <summary>
    /// Return DataTable for particular sql
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql)
    {
        try
        {
            //Declare the datatable and dataadapter object
            DataTable dtData = new DataTable();
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            //Fill the data into dataset from dataadapter
            sqlAdap.SelectCommand = GetDataCommand(sql);
            sqlAdap.Fill(dtData);
            sqlAdap.SelectCommand.Connection.Close();
            sqlAdap.SelectCommand.Dispose();
            sqlAdap.Dispose();

            //Return the datatable
            return dtData;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Close the opened DataReader
    /// </summary>
    /// <param name="sqlDr"></param>
    public static void CloseDataReader(SqlDataReader sqlDr)
    {
        try
        {
            sqlDr.Close();
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
        }
    }

    /// <summary>
    /// Open and Close the SqlConnection
    /// </summary>
    /// <returns></returns>
    public static SqlConnection GetConnection()
    {
        try
        {
            string conStr = ConfigurationManager.ConnectionStrings["GemsConnectionString"].ToString();
            SqlConnection sqlCon = new SqlConnection(conStr);

            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            return sqlCon;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Get the Sql connection state
    /// </summary>
    /// <returns></returns>
    public static bool CheckDBConnection()
    {
        try
        {
            SqlConnection sqlCon = new SqlConnection();

            //Check the sql server connection state
            if (sqlCon.State != ConnectionState.Closed)
            {
                sqlCon.Close();
            }
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
                //CheckDBConnection = true;
                sqlCon.Close();
            }
            return true;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return false;           
        }
    }

    /// <summary>
    /// Get the Connection string from config file
    /// </summary>
    /// <returns></returns>
    public static string GetString()
    {
        try
        {
            //Get the connection from Config file
            string connString;
            connString = ConfigurationManager.ConnectionStrings["GemsConnectionString"].ToString();
            return connString;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return string.Empty;
        }
    }

    /// <summary>
    /// returns  Dataset 
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    /// <remarks></remarks>
    public static DataSet GetDataset(string sql)
    {
        try
        {
            DataSet dsDataset = new DataSet();
            SqlDataAdapter daAdapter;

            daAdapter = new SqlDataAdapter(GetCommand(sql));
            daAdapter.Fill(dsDataset);
            daAdapter.SelectCommand.Connection.Close();
            daAdapter.SelectCommand.Dispose();
            daAdapter.Dispose();

            return dsDataset;
        } 
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    ///  returns  Dataset using parameters
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="hsh"></param>
    /// <returns></returns>
    /// <remarks></remarks>
    public static DataSet GetDataset(string sql, Hashtable hsh)
    {
        try
        {
            DataSet dsDataset = new DataSet();
            SqlDataAdapter daAdapter;

            daAdapter = new SqlDataAdapter(GetCommand(sql, hsh));
            daAdapter.Fill(dsDataset);
            daAdapter.SelectCommand.Connection.Close();
            daAdapter.SelectCommand.Dispose();
            daAdapter.Dispose();
            return dsDataset;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// returns DataReader using parameters
    /// </summary>
    /// <param name="sqlQuery"></param>
    /// <param name="hsh"></param>
    /// <returns></returns>
    /// <remarks></remarks>
    public static SqlDataReader GetDataReader(string sql)
    {
        try
        {
            //Return the datareader
            return GetDataCommand(sql).ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// returns GetCommand using parameters
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="params"></param>
    /// <returns></returns>
    /// <remarks></remarks>
    public static SqlCommand GetCommand(string sql, Hashtable param)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandText = sql;
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandTimeout = 0;
            sqlCmd.Connection = GetConnection();

            if (param.Count > 0)
            {
                foreach (string hshKey in param.Keys)
                {
                    sqlCmd.Parameters.Add(new SqlParameter(hshKey, param[hshKey]));
                }
            }
            return sqlCmd;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    ///  returns GetCommand without using parameters
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    /// <remarks></remarks>
    public static SqlCommand GetCommand(string sql)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand();

            sqlCmd.CommandText = sql;
            //sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Connection = GetConnection();

            return sqlCmd;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return null;
        }
    }

    /// <summary>
    /// Executes the insert data command.
    /// </summary>
    /// <param name="sqlQuery">The SQL query.</param>
    /// <param name="hsh">The HSH.</param>
    public static void ExecuteInsertDataCommand(string sqlQuery, Hashtable hsh)
    {
        try
        {
            SqlCommand sqlcmd = new SqlCommand();

            sqlcmd.CommandText = sqlQuery;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Connection = GetConnection();

            if (hsh.Count > 0)
            {
                foreach (string hshKey in hsh.Keys)
                {
                    sqlcmd.Parameters.Add(new SqlParameter(hshKey, hsh[hshKey]));
                }
            }
            sqlcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);          
        }
    }

    /// <summary>
    /// Returns the insert data command.
    /// </summary>
    /// <param name="sqlQuery">The SQL query.</param>
    /// <param name="hsh">The HSH.</param>
    /// <returns></returns>
    public static long ReturnInsertDataCommand(string sqlQuery, Hashtable hsh)
    {
        try
        {
            SqlCommand sqlcmd = new SqlCommand();

            sqlcmd.CommandText = sqlQuery;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Connection = GetConnection();

            if (hsh.Count > 0)
            {
                foreach (string hshKey in hsh.Keys)
                {
                    sqlcmd.Parameters.Add(new SqlParameter(hshKey, hsh[hshKey]));
                }
                sqlcmd.Parameters.Add("@RETURNVALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            }
            sqlcmd.ExecuteNonQuery();
            sqlcmd.Connection.Close();
            sqlcmd.Connection.Dispose();
            sqlcmd.Dispose();
            int retVal = int.Parse(sqlcmd.Parameters["@RETURNVALUE"].Value.ToString());
            return retVal;
        }
        catch (Exception ex)
        {
            JSAlert.Show(ex.Message);
            return 0;
        }
    }
}

